/*
 * Created on 10.06.2004
 */

package de.localsport.util;

/**
 * Inserts a match or bet result into database.
 * It updates the player's scores according to the new
 * result, too.
 * 
 * @author MaS
 *
 */

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

public class MatchResultUpdator {

	private Connection conn = null;

	public MatchResultUpdator ()
		throws java.sql.SQLException {
		this.openConnection();
	}

	public MatchResultUpdator (Connection conn) {
		this.conn = conn;
	}

	public int updateMatchResult (int match_id, int home_score, int guest_score)
		throws java.sql.SQLException {

		int flag = -1;

		String sSQL =	"UPDATE matches SET " +
						"home_score=" + home_score + ", " +
						"guest_score=" + guest_score + " " +
						"WHERE match_id = " + match_id;

		Statement stat = conn.createStatement();
		flag = stat.executeUpdate(sSQL);
		
		// Call ScoreUpdate
		ScoreUpdator su = new ScoreUpdator(conn);		

		// Set success-flag
		flag = flag * su.updateMatchScores(match_id);
		
		// Update Current_Scores
		flag = flag * this.updateCurrentScore();
		
		// Update Current_Ranks
		flag = flag * this.updateCurrentRank();		
		
		return flag;
	}
	
	public int updateMatchResult (String match_id, String home_score, String guest_score)
		throws java.sql.SQLException {
		
		int i_match_id = -1;
		int i_home_score = -1;
		int i_guest_score = -1;
		int status = 1;
		
		try {
			i_match_id = Integer.parseInt(match_id);
			i_home_score = Integer.parseInt(home_score);
			i_guest_score = Integer.parseInt(guest_score);
		} catch (NumberFormatException n) {
			status = -1;
		}
		if (status == 1) 
			status = updateMatchResult (i_match_id, i_home_score, i_guest_score);
		
		return status;
	}	

	
	public int updateBetResult (int match_id, int home_score)
		throws java.sql.SQLException {

		int flag = -1;
		
		String sSQL =	"UPDATE matches SET " +
						"home_score=" + home_score + " " +
						"WHERE match_id = " + match_id;

		Statement stat = conn.createStatement();
		flag = stat.executeUpdate(sSQL);

		// Call ScoreUpdate
		ScoreUpdator su = new ScoreUpdator(conn);		
		
		// Set success-flag
		flag = flag * su.updateBetScores(match_id);

		// Update Current_Scores
		flag = flag * this.updateCurrentScore();
		
		// Update Current_Ranks
		flag = flag * this.updateCurrentRank();
		
		return flag;
	}	

	public int updateBetResult (String match_id, String home_score)
		throws java.sql.SQLException {
		
		int i_match_id = -1;
		int i_home_score = -1;
		int status = 1;
		
		try {
			i_match_id = Integer.parseInt(match_id);
			i_home_score = Integer.parseInt(home_score);
		} catch (NumberFormatException n) {
			status = -1;
		}
		if (status == 1) 
			status = updateBetResult (i_match_id, i_home_score);
		
		return status;
	}
	
	
	public void openConnection ()
		throws java.sql.SQLException {
		
		this.conn = MySqlConnector.cn();
	}

	
	public void closeConnection () {
			
		try {if (conn != null) this.conn.close();}
		catch (java.sql.SQLException e) {}
	}

	
	public int updateCurrentScore() 
		throws java.sql.SQLException {
		
		int flag = 1;
		
		String sSQL =
			"select member_id as b_member_id, sum(score) as b_curr_score " + 
			"from bets " +
			"where score is not null " +
			"group by member_id";
		
		String uSQL =
			"update members set current_score = ? where member_id = ?";
		
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sSQL);
		
		int bCurrScore = 0;
		int bMemberId = 0;
		
		PreparedStatement uPS = conn.prepareStatement(uSQL);
		
		while (rs.next()) {
			
			bCurrScore = rs.getInt("b_curr_score");
			bMemberId = rs.getInt("b_member_id");
			
			uPS.setInt(1, bCurrScore);
			uPS.setInt(2, bMemberId);
			
			flag = flag * uPS.executeUpdate();
		}
		
		try {
		
			if (uPS != null) uPS.close();
			if (stat != null) stat.close();
			if (rs != null) rs.close();			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		
		return flag;		
	}

	
	
	public int updateCurrentRank() 
		throws java.sql.SQLException {
		
		int flag = 1;

		String sSQL =
			"select m1.member_id as m_member_id, count(m2.last_name) + 1 as new_rank from members m1 " + 
			"left join members m2 on m1.current_score < m2.current_score " +
			"where m1.registered = 1 " +
			"group by m1.member_id";

		String uSQL =
			"update members set current_rank = ? where member_id = ?";
		
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sSQL);
		
		int bNewRank = 0;
		int bMemberId = 0;
		
		PreparedStatement uPS = conn.prepareStatement(uSQL);
		
		while (rs.next()) {
			
			bNewRank = rs.getInt("new_rank");
			bMemberId = rs.getInt("m_member_id");
			
			uPS.setInt(1, bNewRank);
			uPS.setInt(2, bMemberId);
			
			flag = flag * uPS.executeUpdate();
		}
		
		try {	
			if (uPS != null) uPS.close();
			if (stat != null) stat.close();
			if (rs != null) rs.close();			
			
		} catch (Exception ex) {
			ex.printStackTrace();			
		}
		
		return flag;
	}
	
	
	public static void main(String[] args) {
	
	}
}
